package com.yang.dao;

import com.yang.entity.NoteThings;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 事件表(NoteThings)表数据库访问层
 *
 * @author yangdacheng
 * @since 2022-03-02 19:11:03
 */
@Mapper
@Repository
public interface NoteThingsDao {

    /**
     * 添加新的事件
     *
     * @param noteThings
     * @return
     */
    @Insert("INSERT INTO noteThings VALUES(" +
            "#{thingsId},#{thingsName},#{punchCardColor},#{backcolor},#{isPunchCard}," +
            "#{isEmailStart},#{isTelStart},#{startTime},#{endTime},#{isSend},#{thingsType})")
    Boolean insertNoteThings(NoteThings noteThings);

    /**
     * 修改事件
     *
     * @param noteThings
     * @return
     */
    @Update("UPDATE noteThings SET " +
            "thingsName=#{thingsName},punchCardColor=#{punchCardColor},backColor=#{backcolor}," +
            "isPunchCard=#{isPunchCard},isEmailStart=#{isEmailStart},isTelStart=#{isTelStart}," +
            "startTime=#{startTime},endTime=#{endTime},isSend=#{isSend},thingsType=#{thingsType}" +
            "WHERE thingsID=#{thingsId}")
    Boolean updateNoteThings(NoteThings noteThings);

    /**
     * 根据编号删除对应事件
     *
     * @param thingsID
     * @return
     */
    @Delete("DELETE FROM noteThings WHERE thingsID = #{thingsID}")
    Boolean deleteNoteThings(String thingsID);

    /**
     * 查询所有的事件
     *
     * @return
     */
    @Results(value = {
            @Result(column = "thingsID", property = "thingsId", id = true),
            @Result(column = "thingsName", property = "thingsName"),
            @Result(column = "punchCardColor", property = "punchCardColor"),
            @Result(column = "backColor", property = "backcolor"),
            @Result(column = "isPunchCard", property = "isPunchCard"),
            @Result(column = "isEmailStart", property = "isEmailStart"),
            @Result(column = "isTelStart", property = "isTelStart"),
            @Result(column = "startTime", property = "startTime"),
            @Result(column = "endTime", property = "endTime"),
            @Result(column = "isSend", property = "isSend"),
            @Result(column = "thingsType", property = "thingsType"),
    }, id = "noteThingsMapper")
    @Select("SELECT * FROM noteThings")
    List<NoteThings> queryAllThings();

    /**
     * 根据事件编号获取事件
     *
     * @param thingsID
     * @return
     */
    @ResultMap(value = "noteThingsMapper")
    @Select("SELECT * FROM noteThings WHERE thingsID = #{thingsID}")
    NoteThings queryThingsByID(String thingsID);

    /**
     * 查询用户下未打卡事件数量
     *
     * @param thingsID
     * @return
     */
    @Select("SELECT isPunchCard FROM noteThings WHERE thingsID = #{thingsID}")
    Integer queryPunchByState(String thingsID);

    /**
     * 获取当天的未打卡的数据并且为发送的数据 并且按照时间顺序进行排序
     *
     * @return
     */
    @ResultMap(value = "noteThingsMapper")
    @Select("SELECT * FROM noteThings WHERE isPunchCard = 0 AND isSend = 0 AND " +
            "DATE(startTime)= CURDATE() " +
            "AND startTime >= NOW() ORDER BY startTime ASC")
    List<NoteThings> queryAllThingNoPunch();


    /**
     * 获取多少天前发送与打卡的比例
     *
     * @param userID     用户编号
     * @param beforeDay  多少天前
     * @param isSend     是否发送
     * @param punchState 是否大侃
     * @return
     */
    @Select("SELECT CONVERT(" +
            "(SELECT COUNT(*) FROM noteThings INNER JOIN userThings ON noteThings.thingsID = userThings.thingsID " +
            "WHERE TO_DAYS( NOW( ) ) - TO_DAYS(startTime) = #{beforeDay} AND isSend = #{isSend} AND " +
            "noteThings.isPunchCard = #{punchState} AND  userThings.userID = #{userID})/" +
            "(SELECT COUNT(*) FROM noteThings INNER JOIN userThings ON noteThings.thingsID = userThings.thingsID " +
            "WHERE TO_DAYS( NOW( ) ) - TO_DAYS(startTime) = #{beforeDay} AND isSend = #{isSend} AND " +
            "userThings.userID = #{userID}),DECIMAL(15,2))")
    Double queryPunchConvertSend(String userID, Integer beforeDay, Integer isSend, Integer punchState);

    /**
     * 例：查询今天之后为发送的数据类型为邮箱发送状态为打开
     * @param userID 用户编号
     * @param state  状态
     * @return
     */
    @Select("SELECT COUNT(*) FROM noteThings INNER JOIN userThings ON noteThings.thingsID = userThings.thingsID " +
            "WHERE NOW() < startTime AND isEmailStart = #{state} AND userThings.userID = #{userID}")
    Integer queryNumberByEmailAfter(String userID, Integer state);

    /**
     * 查询短信为发送数量
     * @param userID
     * @param state
     * @return
     */
    @Select("SELECT COUNT(*) FROM noteThings INNER JOIN userThings ON noteThings.thingsID = userThings.thingsID " +
            "WHERE NOW() < startTime AND isTelStart = #{state} AND userThings.userID = #{userID}")
    Integer queryNumberBySmsAfter(String userID,Integer state);
}